﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using MODEL;
namespace DAL
{
    public class DalChuku
    {
        public List<ChuKu> GetListByPy(string py)
        {
            string sql = "select id,classid,dicid,goodname,goodsnumber,price,createdate,goodspy from chuku where tui=0 and goodspy like @py";
            DataTable dt = AccessHelp.ExecuteReader(sql, new OleDbParameter("@py", "%"+py+"%"));
            return TableToList(dt);
        }
        public List<ChuKu> TableToList(DataTable dt)
        {
            List<ChuKu> chukus = new List<ChuKu>();
            foreach (DataRow row in dt.Rows)
            {
                ChuKu c = new ChuKu();
                c.Id = (int)row[0];
                c.ClassId = (int)row[1];
                c.DicId = (int)row[2];
                c.GoodName = row[3].ToString();
                c.Number = (int)row[4];
                c.Price = (int)row[5];
                c.Date = DateTime.Parse(row[6].ToString());
                //c.Remark = row[7].ToString();
                c.PY = row[7].ToString();
                chukus.Add(c);
            }
            return chukus;
        }

        public ChuKu GetById(int dicid)
        {
            string sql = "select id,classid,dicid,goodname,goodsnumber,price,createdate,goodspy from chuku where tui=0 and dicid=@dicid";
            DataTable dt = AccessHelp.ExecuteReader(sql, new OleDbParameter("@dicid", dicid));
            return TableToList(dt).FirstOrDefault();
        }

        public DataTable GetList(string py, DateTime startDate, DateTime endDate)
        {
            string sql = "select c.id,d.goodsname,d.goodsmodel,d.goodsspe,d.goodsunit,c.classid,c.dicid,c.goodsnumber,c.price,c.createdate,c.remark,d.goodspy,c.tui from chuku c inner join dic d on c.dicid = d.id where d.goodspy like @goodspy and (c.createdate >= @startDate and c.createdate <= @endDate) order by c.createdate ";
            DateTime sDate = startDate;
            DateTime eDate = endDate.AddDays(1).AddMilliseconds(-1000);
            OleDbParameter[] pms = new OleDbParameter[] 
            {
                new OleDbParameter("@goodspy", "%" + py + "%"),
                new OleDbParameter("@startDate",sDate.ToShortDateString()),
                new OleDbParameter("@endDate",eDate.ToShortDateString())
            };
            DataTable dt = AccessHelp.ExecuteReader(sql, pms);
            //return TableToList(dt);
            return dt;
        }

        public List<ChuKu> GetObjectByDicId(int dicid, DateTime date)
        {
            string sql = "select id,classid,dicid,goodname,goodsnumber,price,createdate,goodspy from chuku where tui=0 and dicid=@dicid and (createdate >= @startDate and createdate <= @endDate)";
            string startDate = date.Date.ToString();
            string endDate = date.Date.AddMonths(1).AddSeconds(-1).ToString();
            OleDbParameter[] pms = new OleDbParameter[] 
            {                
                new OleDbParameter("@dicid",dicid),
                new OleDbParameter("@startDate",startDate),
                new OleDbParameter("@endDate",endDate)
            };
            DataTable dt = AccessHelp.ExecuteReader(sql,pms);
            return TableToList(dt);
        }
    }
}
